﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using MySql.Data.MySqlClient;

namespace data_upload_HCollage
{
    public partial class UploadMarksForm : Form
    {
        public UploadMarksForm()
        {
            InitializeComponent();
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            if (upStd_openFileDialog.ShowDialog() == DialogResult.OK)
            {
                txtFileLocation.Text = upStd_openFileDialog.FileName;
            }
        }

        private void btnUploadmarks_Click(object sender, EventArgs e)
        {
           if(cmbexam.Text.Equals("First Term"))
              uploadFirstTermMarks();
            if(cmbexam.Text.Equals("Promotion Test"))
              uploadPro_test_Marks();
            if(cmbexam.Text.Equals("Pre-Test"))
              uploadPre_test_Marks();
            if(cmbexam.Text.Equals("Test-Exam"))
              upload_Text_exam_Marks();
        }

        private void uploadFirstTermMarks()
        {
            String update_sql = "";
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from first_term", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                string student_id = "";
                string academic_session = "";
                string group_id = cmbexam.Text;
                string subj = "";
                string att = "";
                string ct = "";
                string mcq = "";
                string cq = "";
                string h_total = "";
                string g_total = "";
               
                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    subj = row[2].ToString();
                    att = row[3].ToString();
                    ct = row[4].ToString();
                    mcq = row[5].ToString();
                    cq = row[6].ToString();
                    h_total = row[7].ToString();
                    g_total = row[8].ToString();

                    att = att == "" ? "null" : att;
                    ct = ct == "" ? "null" : ct;
                    mcq = mcq == "" ? "null" : mcq;
                    cq = cq == "" ? "null" : cq;
                    h_total = h_total == "" ? "null" : h_total;
                    g_total = g_total == "" ? "null" : g_total;


                     update_sql = " update subj_marks_detail set "
                                        +" f_y_1st_att="+att
                                        +", f_y_1st_ct="+ct
                                        +", f_y_1st_ht_mcq="+mcq
                                        +", f_y_1st_ht_cq="+cq
                                        +", f_y_1st_ht_total="+h_total
                                        +", f_y_1st_gran_total="+g_total
                                        +" where student_id='"+student_id+"' and academic_session='"+academic_session+"' and subject_id='"+subj+"'";

                    MySqlCommand Cmd_update = new MySqlCommand(update_sql, connMysql);
                    Cmd_update.ExecuteNonQuery();
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(update_sql);
            }

            MessageBox.Show("Done!!!!!!!!");
        }
        ///////////////////////////////////////////////////
        //////////////////////////////////////////////////////////
        private void uploadPro_test_Marks()
        {
            String update_sql = "";
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from pro_test", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                string student_id = "";
                string academic_session = "";
                string group_id = cmbexam.Text;
                string subj = "";
                string att = "";
                string mcq = "";
                string cq = "";
                string prac = "";
                string h_total = "";
                string gpa = "";

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    subj = row[2].ToString();
                    att = row[3].ToString();
                    mcq = row[4].ToString();
                    cq = row[5].ToString();
                    prac=row[6].ToString();
                    h_total = row[7].ToString();
                    gpa = row[8].ToString();

                    att = att == "" ? "null" : att;
                    mcq = mcq == "" ? "null" : mcq;
                    cq = cq == "" ? "null" : cq;
                    prac = prac == "" ? "null" : prac;
                    h_total = h_total == "" ? "null" : h_total;
                    gpa = gpa == "" ? "null" : gpa;


                    update_sql = " update subj_marks_detail set "
                                       + " f_y_pro_att=" + att
                                       + ", f_y_pro_ht_mcq=" + mcq
                                       + ", f_y_pro_ht_cq=" + cq
                                       + ", f_y_pro_ht_prac=" + prac
                                       + ", f_y_pro_ht_total=" + h_total
                                       + ", f_y_pro_gpa=" + gpa
                                       + " where student_id='" + student_id + "' and academic_session='" + academic_session + "' and subject_id='" + subj + "'";

                    MySqlCommand Cmd_update = new MySqlCommand(update_sql, connMysql);
                    Cmd_update.ExecuteNonQuery();
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(update_sql);
            }

            MessageBox.Show("Done!!!!!!!!");
        }
        ////////////////////////////////////////////////////////////
        /////////////////////////////////////////////////////////////
        private void uploadPre_test_Marks()
        {
            String update_sql = "";
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from pre_test", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                string student_id = "";
                string academic_session = "";
                string group_id = cmbexam.Text;
                string subj = "";
                string att = "";
                string ct = "";
                string mcq = "";
                string cq = "";
                string h_total = "";
                string g_total = "";

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    subj = row[2].ToString();
                    att = row[3].ToString();
                    ct = row[4].ToString();
                    mcq = row[5].ToString();
                    cq = row[6].ToString();
                    h_total = row[7].ToString();
                    g_total = row[8].ToString();

                    att = att == "" ? "null" : att;
                    ct = ct == "" ? "null" : ct;
                    mcq = mcq == "" ? "null" : mcq;
                    cq = cq == "" ? "null" : cq;
                    h_total = h_total == "" ? "null" : h_total;
                    g_total = g_total == "" ? "null" : g_total;


                    update_sql = " update subj_marks_detail set "
                                       + " s_y_pre_att=" + att
                                       + ", s_y_pre_ct=" + ct
                                       + ", s_y_pre_ht_mcq=" + mcq
                                       + ", s_y_pre_ht_cq=" + cq
                                       + ", s_y_pre_ht_total=" + h_total
                                       + ", s_y_pre_gran_total=" + g_total
                                       + " where student_id='" + student_id + "' and academic_session='" + academic_session + "' and subject_id='" + subj + "'";

                    MySqlCommand Cmd_update = new MySqlCommand(update_sql, connMysql);
                    Cmd_update.ExecuteNonQuery();
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(update_sql);
            }

            MessageBox.Show("Done!!!!!!!!");
        }
        ///////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////
        private void upload_Text_exam_Marks()
        {
            String update_sql = "";
            try
            {
                //OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=Excel 8.0");
                OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtFileLocation.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
                OleDbDataAdapter da = new OleDbDataAdapter("select * from test_exam", conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                int i = 0;
                MySqlConnection connMysql = new MySqlConnection(dbConnection.ConStr);
                connMysql.Open();
                string student_id = "";
                string academic_session = "";
                string group_id = cmbexam.Text;
                string subj = "";
                string att = "";
                string prac_att= "";
                string p1_mcq = "";
                string p1_cq = "";
                string p1_total = "";
                string p2_mcq = "";
                string p2_cq = "";
                string p2_total = "";
                string g_total = "";
                string gpa = "";

                foreach (DataRow row in dt.Rows)
                {
                    student_id = row[0].ToString();
                    academic_session = row[1].ToString();
                    subj = row[2].ToString();
                    att = row[3].ToString();
                    prac_att = row[4].ToString();
                    p1_mcq = row[5].ToString();
                    p1_cq = row[6].ToString();
                    p1_total = row[7].ToString();
                    p2_mcq = row[8].ToString();
                    p2_cq = row[9].ToString();
                    p2_total = row[10].ToString();
                    g_total = row[11].ToString();
                    gpa = row[12].ToString();

                    att = att == "" ? "null" : att;
                    prac_att = prac_att == "" ? "null" : prac_att;
                    p1_mcq = p1_mcq == "" ? "null" : p1_mcq;
                    p1_cq = p1_cq == "" ? "null" : p1_cq;
                    p1_total = p1_total == "" ? "null" : p1_total;
                    p2_mcq = p2_mcq == "" ? "null" : p2_mcq;
                    p2_cq = p2_cq == "" ? "null" : p2_cq;
                    p2_total = p2_total == "" ? "null" : p2_total;
                    g_total = g_total == "" ? "null" : g_total;
                    gpa = gpa == "" ? "null" : gpa;


                    update_sql = " update subj_marks_detail set "
                                       + " s_y_test_att=" + att
                                       + ", s_y_prac_att=" + prac_att
                                       + ", s_y_t_p1_mcq=" + p1_mcq
                                       + ", s_y_t_p1_cq=" + p1_cq
                                       + ", s_y_t_p1_total=" + p1_total
                                       + ", s_y_t_p2_mcq=" + p2_mcq
                                       + ", s_y_t_p2_cq=" + p2_cq
                                       + ", s_y_t_p2_total=" + p2_total
                                       + ", s_y_test_gra_total=" + g_total
                                       + ", s_y_test_gpa=" + gpa
                                       + " where student_id='" + student_id + "' and academic_session='" + academic_session + "' and subject_id='" + subj + "'";

                    MySqlCommand Cmd_update = new MySqlCommand(update_sql, connMysql);
                    Cmd_update.ExecuteNonQuery();
                }
                connMysql.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show(update_sql);
            }

            MessageBox.Show("Done!!!!!!!!");
        }
        //////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////
    }
}
